package com.epoint.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.druid.util.StringUtils;
import com.epoint.entity.ProductInfo;
import com.epoint.utils.JDBCUtils;

/**
 * @Description 操作产品信息表
 * @author ylc
 */
public class ProductInfoDao {
	/**
	 * 获取产品信息列表
	 * @param productName
	 * @param productUsage
	 * @param pageIndex
	 * @param pageSize
	 * @param sortField
	 * @param sortOrder
	 * @return
	 */
	public List<ProductInfo> getProductList(String productName, String productUsage, int pageIndex, int pageSize, String sortField,
			String sortOrder) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		List<ProductInfo> productList = new ArrayList<ProductInfo>();
		List<Object> params = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("select * from productinfo where 1=1");

		// 查询条件
		if (!StringUtils.isEmpty(productName)) {
			sb.append(" and productname like '%" + productName + "%'");
		}
		if (!StringUtils.isEmpty(productUsage)) {
			sb.append(" and productUsage = ?");
			params.add(productUsage);
		}
		// 排序条件
		if (!StringUtils.isEmpty(sortOrder) && !StringUtils.isEmpty(sortField)) {
			sb.append(" order by " + sortField + " " + sortOrder);
		}
		// 分页条件
		if (-1 != pageIndex && -1 != pageSize) {
			sb.append(" limit ?,?");
			params.add(pageIndex * pageSize);
			params.add(pageSize);
		}
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = sb.toString();
			pstmt = conn.prepareStatement(sql);

			// 给?赋值
			int sizeCount = params.size();
			for (int i = 0; i < sizeCount; i++) {
				if ((sizeCount - i) <= 2) {
					pstmt.setInt(i + 1, Integer.parseInt(params.get(i).toString()));
				} else {
					pstmt.setString(i + 1, params.get(i).toString());
				}
			}
			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				ProductInfo productInfo = new ProductInfo();
				setProductInfo(productInfo,rs);
				productList.add(productInfo);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}

		return productList;
	}
	/**
	 * 获取产品列表总数
	 * @param productName
	 * @param productUsage
	 * @return
	 */
	public int getProductListCount(String productName, String productUsage) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count = 0;

		List<Object> params = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("select count(1) from productinfo where 1=1");

		// 查询条件
		if (!StringUtils.isEmpty(productName)) {
			sb.append(" and productName like '%" + productName + "%'");
		}
		if (!StringUtils.isEmpty(productUsage)) {
			sb.append(" and productUsage = ?");
			params.add(productUsage);
		}

		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = sb.toString();
			pstmt = conn.prepareStatement(sql);

			// 给?赋值
			int sizeCount = params.size();
			for (int i = 0; i < sizeCount; i++) {
				if ((sizeCount - i) <= 2) {
					pstmt.setInt(i + 1, Integer.parseInt(params.get(i).toString()));
				} else {
					pstmt.setString(i + 1, params.get(i).toString());
				}
			}
			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return count;
	}
	/**
	 * 根据Id获取产品信息
	 * @return
	 */
	public ProductInfo getProductInfo(String productId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ProductInfo productInfo = new ProductInfo();
		
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select * from productinfo where productId = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, productId);
			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				setProductInfo(productInfo, rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return productInfo;
	}
	/**
	 * 根据Id查询产品信息
	 * @param productId
	 * @return
	 */
	public ProductInfo findById(String productId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ProductInfo productInfo = new ProductInfo();
		
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "SELECT productid, productname, country, `language`, productusage, createtime, count, quote, remark FROM quotationManage.productinfo where productid = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, productId);
			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				setProductInfo(productInfo, rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return productInfo;
	}
	
	/**
	 * 根据Id删除产品信息及其报价信息
	 * @param productId
	 * @return
	 */
	public int deleteProductByProductId(String productId) {
		Connection conn = null;
		PreparedStatement pstmtProduct = null;
		PreparedStatement pstmtQuote = null;
		int res = 0;
		
		try {
			conn = JDBCUtils.getConnection();
			// 关闭自动提交，开启事务
			conn.setAutoCommit(false);
			// 删除报价信息
			String sql2 = "DELETE FROM quotationManage.quoteinfo WHERE productid=?;";
			pstmtQuote = conn.prepareStatement(sql2);
			pstmtQuote.setString(1, productId);
			res = pstmtQuote.executeUpdate();
			
			// 删除产品信息
			String sql1 = "DELETE FROM quotationManage.productinfo WHERE productid=?;";
			pstmtProduct = conn.prepareStatement(sql1);
			pstmtProduct.setString(1, productId);
			res = pstmtProduct.executeUpdate();
			
			// 提交
			conn.commit();
		} catch (SQLException e) {
			// 回滚
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			JDBCUtils.close(pstmtQuote);
			JDBCUtils.close(pstmtProduct, conn);
		}
		return res;
	}
	
	/**
	 * 将查询到的产品信息封装为对象
	 * @param productInfo
	 * @param rs
	 * @throws SQLException
	 */
	private void setProductInfo(ProductInfo productInfo, ResultSet rs) throws SQLException{
		productInfo.setProductId(rs.getString("productId"));
		productInfo.setProductName(rs.getString("productName"));
		productInfo.setCountry(rs.getInt("country"));
		productInfo.setLanguage(rs.getString("language"));
		productInfo.setProductUsage(rs.getInt("productUsage"));
		productInfo.setCreateTime(rs.getDate("createTime"));
		productInfo.setCount(rs.getInt("count"));
		productInfo.setQuote(rs.getBigDecimal("quote"));
		productInfo.setRemark(rs.getString("remark"));
	}
	/**
	 * 返回最大产品编号
	 * @return
	 */
	public String getMaxProductId() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String res = "";
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select productId from productInfo order by productId desc limit 1";
			pstmt = conn.prepareStatement(sql);

			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				res = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return res;
	}
	/**
	 * 添加产品信息
	 * @param productInfo
	 * @return
	 */
	public int addProductInfo(ProductInfo productInfo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int res = 0;
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "INSERT INTO quotationManage.productinfo (productid, productname, country, `language`, productusage, createtime, count, quote, remark) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, productInfo.getProductId());
			pstmt.setString(2, productInfo.getProductName());
			pstmt.setInt(3, productInfo.getCountry());
			pstmt.setString(4, productInfo.getLanguage());
			pstmt.setInt(5, productInfo.getProductUsage());
			pstmt.setDate(6, productInfo.getCreateTime());
			pstmt.setInt(7, productInfo.getCount());
			pstmt.setBigDecimal(8, productInfo.getQuote());
			pstmt.setString(9, productInfo.getRemark());
			
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(pstmt, conn);
		}
		return res;
	}
	/**
	 * 判断产品名称是否已经存在
	 * @param productName
	 * @return
	 */
	public int isSameName(String productName) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int res = 0;
		
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select count(1) from productinfo where productname = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, productName);
			
			// 执行查询，获取结果
			rs = pstmt.executeQuery();
			while(rs.next()) {
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return res;
	}
	/**
	 * 获取所有产品名称
	 * @return
	 */
	public List<ProductInfo> getAllProducts() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<ProductInfo> res = new ArrayList<>();
		
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select productId, productName from productinfo where 1=1";
			pstmt = conn.prepareStatement(sql);
			
			// 执行查询，获取结果
			rs = pstmt.executeQuery();
			while(rs.next()) {
				ProductInfo productInfo = new ProductInfo();
				productInfo.setProductId(rs.getString("productId"));
				productInfo.setProductName(rs.getString("productName"));
				res.add(productInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return res;
	}
	/**
	 * 更新产品信息
	 * @param productInfo
	 * @return
	 */
	public int updateProductInfo(ProductInfo productInfo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int res = 0;
		
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "update productinfo set productUsage = ?, remark = ? where productid = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, productInfo.getProductUsage());
			pstmt.setString(2, productInfo.getRemark());
			pstmt.setString(3, productInfo.getProductId());
			
			res = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(pstmt, conn);
		}
		return res;
	}

}
